﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using Meeting.DBUtility;
using System.Data.SqlClient;
using Newtonsoft.Json;
using Meeting.Public;

namespace Meeting.BLL
{
    /// <summary>
    /// 会议流程一些函数
    /// </summary>
    public class MeetingExt
    {

        public string errorString = "";

        #region 会议编辑相关函数
        public string GetMeetingById(string Id)
        {
            string sql = @"Select *
                    ,(Select Name From MeetingCategory Where Id = CategoryId) CategoryName
                    ,(Select Name From Room Where Id = RoomId) RoomName
                    ,(Select Name From VideoRoom Where Id = VideoRoomId) VideoRoomName  From Meeting";
            sql += " where Id = " + Id;
            DataSet ds = DbHelperSQL.Query(sql);
            if (ds == null) return "";
            Model.JsonResponse jg = new Model.JsonResponse();
            jg.success = true;
            jg.ResponseData = ds.Tables[0];
            Newtonsoft.Json.Converters.IsoDateTimeConverter timeConverter = new Newtonsoft.Json.Converters.IsoDateTimeConverter();
            timeConverter.DateTimeFormat = "yyyy'-'MM'-'dd' 'HH':'mm':'ss";
            return JsonConvert.SerializeObject(jg, timeConverter);
        }
        #endregion


        //
        public string GetDeptTreeJson()
        {
            DataSet ds = DbHelperSQL.Query("Select * From [Department] Where IsActive=1 ");
            if (ds == null || ds.Tables[0].Rows.Count < 1) return "";
            GetJson(ds);
            return sReturn;
            //
        }

        string sReturn = "";
        private void GetJson(DataSet ds)
        {
            DataRow[] drs = ds.Tables[0].Select("ParentId=0");
            if (drs.Length < 1) return;
            sReturn = "[";
            foreach (DataRow dr in drs)
            {
                sReturn += "{\"id\":" + dr["id"].ToString() + ",\"iconCls\":\"icon-blank\",\"text\":\"" + dr["Name"].ToString() + "\",\"children\":[";
                GetSub(ds, dr["id"].ToString());
                sReturn += "]},";
            }
            if (sReturn.Length > 0) sReturn = sReturn.Substring(0, sReturn.Length - 1);
            sReturn += "]";
        }
        private void GetSub(DataSet ds, string parentId)
        {
            DataRow[] drs = ds.Tables[0].Select("ParentId=" + parentId);
            if (drs.Length > 0)
            {
                // sReturn += "";
                foreach (DataRow dr in drs)
                {
                    sReturn += "{\"id\":" + dr["id"].ToString() + ",\"iconCls\":\"icon-blank\",\"text\":\"" + dr["Name"].ToString() + "\",\"children\":[";
                    GetSub(ds, dr["id"].ToString());
                    sReturn += "]},";
                }
                if (sReturn.Length > 0) sReturn = sReturn.Substring(0, sReturn.Length - 1);
            }
        }

        //获取会议类型
        public string GetCategoryJson()
        {
            return GetCategoryJson("");
        }
        public string GetCategoryJson(string Id)
        {
            string sql = "Select * From MeetingCategory ";
            if (Id != "") sql += " Where Id = " + Id;
            try
            {
                DataSet ds = DbHelperSQL.Query(sql);
                return JsonConvert.SerializeObject(ds.Tables[0]);
            }
            catch (Exception)
            {
                return "";
            }
        }
        //获取会议室
        public string GetRoomJson()
        {
            string sql = "Select * From Room ";
            try
            {
                DataSet ds = DbHelperSQL.Query(sql);
                return JsonConvert.SerializeObject(ds.Tables[0]);
            }
            catch (Exception)
            {
                return "";
            }
        }
        //视频会议室
        public string GetVideoRoomJson()
        {
            string sql = "Select * From VideoRoom where IsTemp=0";
            try
            {
                DataSet ds = DbHelperSQL.Query(sql);
                return JsonConvert.SerializeObject(ds.Tables[0]);
            }
            catch (Exception)
            {
                return "";
            }
        }

        /// <summary>
        /// 保存会议基本信息
        /// </summary>
        public int MeetingInfoAdd(Meeting.Model.Meeting model)
        {
            try
            {
                if (model.Id == 0) //新增一条
                {
                    StringBuilder strSql = new StringBuilder();
                    strSql.Append("insert into Meeting(");
                    strSql.Append("Name,HostId,HostName,HostTitle,HosetRoleId,StartDate,EndDate,RoomId,Duration,Status,CategoryId,EmployeeId,EmployeeName,EmployeeTitle,RoleId,CreateDate,Comment,IsActive,VideoRoomId)");
                    strSql.Append(" values (");
                    strSql.Append("@Name,@HostId,@HostName,@HostTitle,@HosetRoleId,@StartDate,@EndDate,@RoomId,@Duration,@Status,@CategoryId,@EmployeeId,@EmployeeName,@EmployeeTitle,@RoleId,@CreateDate,@Comment,@IsActive,@VideoRoomId)");
                    strSql.Append(";select @@IDENTITY");
                    SqlParameter[] parameters = {
					new SqlParameter("@Name", SqlDbType.NVarChar,256),
					new SqlParameter("@HostId", SqlDbType.NVarChar,64),
					new SqlParameter("@HostName", SqlDbType.NVarChar,64),
					new SqlParameter("@HostTitle", SqlDbType.NVarChar,64),
					new SqlParameter("@HosetRoleId", SqlDbType.NVarChar,1024),
					new SqlParameter("@StartDate", SqlDbType.DateTime),
					new SqlParameter("@EndDate", SqlDbType.DateTime),
					new SqlParameter("@RoomId", SqlDbType.Int,4),
					new SqlParameter("@Duration", SqlDbType.Decimal,9),
					new SqlParameter("@Status", SqlDbType.Int,4),
					new SqlParameter("@CategoryId", SqlDbType.Int,4),
					new SqlParameter("@EmployeeId", SqlDbType.NVarChar,64),
					new SqlParameter("@EmployeeName", SqlDbType.NVarChar,64),
					new SqlParameter("@EmployeeTitle", SqlDbType.NVarChar,64),
					new SqlParameter("@RoleId", SqlDbType.NVarChar,1024),
					new SqlParameter("@CreateDate", SqlDbType.DateTime),
					new SqlParameter("@Comment", SqlDbType.NVarChar,200),
					new SqlParameter("@IsActive", SqlDbType.Bit,1),
                    new SqlParameter("@VideoRoomId", SqlDbType.UniqueIdentifier,16)};
                    parameters[0].Value = model.Name;
                    parameters[1].Value = model.HostId;
                    parameters[2].Value = model.HostName;
                    parameters[3].Value = model.HostTitle;
                    parameters[4].Value = model.HosetRoleId;
                    parameters[5].Value = model.StartDate;
                    parameters[6].Value = model.EndDate;
                    parameters[7].Value = model.RoomId;
                    parameters[8].Value = model.Duration;
                    parameters[9].Value = model.Status;
                    parameters[10].Value = model.CategoryId;
                    parameters[11].Value = model.EmployeeId;
                    parameters[12].Value = model.EmployeeName;
                    parameters[13].Value = model.EmployeeTitle;
                    parameters[14].Value = model.RoleId;
                    parameters[15].Value = model.CreateDate;
                    parameters[16].Value = model.Comment;
                    parameters[17].Value = model.IsActive;
                    parameters[18].Value = model.VideoRoomId;
                    object obj = DbHelperSQL.GetSingle(strSql.ToString(), parameters);
                    if (obj == null)
                    {
                        return 0;
                    }
                    else
                    {
                        return Convert.ToInt32(obj);
                    }
                }
                else //修改记录
                {
                    StringBuilder strSql = new StringBuilder();
                    strSql.Append("update Meeting set ");
                    strSql.Append("Name=@Name,");
                    strSql.Append("StartDate=@StartDate,");
                    strSql.Append("EndDate=@EndDate,");
                    strSql.Append("RoomId=@RoomId,");
                    strSql.Append("Duration=@Duration,");
                    strSql.Append("Status=@Status,");
                    strSql.Append("CategoryId=@CategoryId,");
                    strSql.Append("Comment=@Comment,");
                    strSql.Append("IsActive=@IsActive,");
                    strSql.Append("VideoRoomId=@VideoRoomId");
                    strSql.Append(" where Id=@Id");
                    SqlParameter[] parameters = {
					new SqlParameter("@Name", SqlDbType.NVarChar,256),
					new SqlParameter("@StartDate", SqlDbType.DateTime),
					new SqlParameter("@EndDate", SqlDbType.DateTime),
					new SqlParameter("@RoomId", SqlDbType.Int,4),
					new SqlParameter("@Duration", SqlDbType.Decimal,9),
					new SqlParameter("@Status", SqlDbType.Int,4),
					new SqlParameter("@CategoryId", SqlDbType.Int,4),
					new SqlParameter("@Comment", SqlDbType.NVarChar,200),
					new SqlParameter("@IsActive", SqlDbType.Bit,1),
                    new SqlParameter("@VideoRoomId", SqlDbType.UniqueIdentifier,16),
					new SqlParameter("@Id", SqlDbType.Int,4)};
                    parameters[0].Value = model.Name;
                    parameters[1].Value = model.StartDate;
                    parameters[2].Value = model.EndDate;
                    parameters[3].Value = model.RoomId;
                    parameters[4].Value = model.Duration;
                    parameters[5].Value = model.Status;
                    parameters[6].Value = model.CategoryId;
                    parameters[7].Value = model.Comment;
                    parameters[8].Value = model.IsActive;
                    parameters[9].Value = model.VideoRoomId;
                    parameters[10].Value = model.Id;

                    int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
                    if (rows > 0)
                    {
                        return model.Id;
                    }
                    else
                    {
                        return 0;
                    }
                }
            }
            catch (Exception ex)
            {
                errorString = ex.Message;
                return 0;
            }
        }
        //保存会议人 
        public bool SaveMeetingEmployee(string meetId, string Host, string HostId, string RoleId, string HostTitle, string selEmployee)
        {
            //生成更新会议主持人
            List<string> sqlList = new List<string>();

            string sql = " Update Meeting Set HostId ='" + HostId + "', HostName='" + CommonFunction.removeAttackOfSql(Host) + "',";
            sql += " HosetRoleId = '" + RoleId + "',HostTitle='" + HostTitle + "' Where Id =" + meetId + " ";
            sqlList.Add(sql);
            sql = " Delete From MeetingEmployee Where MeetingId = " + meetId;//删除已有人员
            sqlList.Add(sql);
            List<Model.StandMeetingEmployee> listEmp = JsonConvert.DeserializeObject<List<Model.StandMeetingEmployee>>(selEmployee);
            //子表
            foreach (Model.StandMeetingEmployee myEmp in listEmp)
            {
                sql = " Insert Into MeetingEmployee(MeetingId ,EmployeeId ,EmployeeName ,EmployeeTitle ,RoleId ,LoginDate,Sort ,Mobile) Values(" + meetId + ",'" + myEmp.EmployeeId + "','" + myEmp.EmployeeName + "','" + myEmp.EmployeeTitle
                    + "','" + myEmp.RoleId + "',null," + myEmp.Sort + ",'" + myEmp.Mobile + "') ";
                sqlList.Add(sql);
            }

            try
            {
                if (DbHelperSQL.ExecuteSqlTran(sqlList) > 0)
                    return true;
                errorString = DbHelperSQL.errorString;
                return false;
            }
            catch (Exception ex)
            {
                errorString = ex.Message;
                return false;
            }
        }

        public string GetSelEmployee(string meetingId, string name)
        {
            string sql = "Select  [EmployeeId] ,[EmployeeName] ,[EmployeeTitle] ,[RoleId],[Sort],[Mobile] From MeetingEmployee Where MeetingId = " + meetingId;
            if (name != "")
            {
                sql += " And EmployeeName Like '%" + name + "%' ";
            }
            sql += " Order by Sort ";
            DataSet ds = DbHelperSQL.Query(sql);
            if (ds == null) return "";
            Model.JsonGrid jGrid = new Model.JsonGrid();
            jGrid.total = ds.Tables[0].Rows.Count;
            jGrid.rows = ds.Tables[0];

            return JsonConvert.SerializeObject(jGrid);
        }
        //会议议题
        public bool SaveMeetingTopic(string meetingId, string json)
        {
            List<TopicSave> temp = new List<TopicSave>();
            temp = JsonConvert.DeserializeObject<List<TopicSave>>(json);
            if (meetingId == "")
            {
                errorString = "没有找到会议，请先保存会议！";
                return false;
            }
            List<string> sqlList = new List<string>();
            string sql = "Delete From MeetingTopic Where MeetingId ='" + meetingId + "' ";
            sqlList.Add(sql);
            foreach (TopicSave te in temp)
            {
                te.Sort = CommonFunction.varToIntDef(te.Sort, 1000).ToString();
                sql = " Insert MeetingTopic(MeetingId,TopicId,EmployeeId,EmployeeName,EmployeeTitle,RoleId,Sort) ";
                sql += " Values('" + meetingId + "','" + te.TopicId + "','" + te.EmployeeId + "','" + te.EmployeeName + "','" + te.EmployeeTitle + "',";
                sql += " '" + te.RoleId + "'," + te.Sort + ")";
                sqlList.Add(sql);
                //设置关联文档
                DataSet ds = GetTopicDoc(te.TopicId);
                if (ds == null)
                {
                    errorString = "获取议题文档出错";
                    return false;
                }
                sql = " Delete From MeetingTopicDocument Where TopicId = '" + te.TopicId + "' ";
                sqlList.Add(sql);
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    sql = " Insert Into MeetingTopicDocument(MeetingId,TopicId,DocumentId) Values('" + meetingId + "','" + te.TopicId + "','" + dr["Id"].ToString() + "')";
                    sqlList.Add(sql);
                }
                //设置会议地块
                ds = GetTopicPlate(te.TopicId);
                if (ds == null)
                {
                    errorString = "获取议题地块出错";
                    return false;
                }
                sql = " Delete From MeetingTopicPlate Where TopicId = '" + te.TopicId + "' ";
                sqlList.Add(sql);
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    sql = " Insert Into MeetingTopicPlate(MeetingId,TopicId,PlateId) Values('" + meetingId + "','" + te.TopicId + "','" + dr["Id"].ToString() + "')";
                    sqlList.Add(sql);
                }
            }

            //
            if (DbHelperSQL.ExecuteSqlTran(sqlList) > 0)
                return true;
            errorString = DbHelperSQL.errorString;
            return false;
        }

        private DataSet GetTopicDoc(string topicId)
        {
            try
            {
                string sql = " Select Id From TopicDocument where TopicId = '" + topicId + "' ";
                return DbHelperSQL.Query(sql);
            }
            catch
            {
                return null;
            }
        }
        private DataSet GetTopicPlate(string topicId)
        {
            try
            {
                string sql = " Select Id From TopicPlate where TopicId = '" + topicId + "' ";
                return DbHelperSQL.Query(sql);
            }
            catch
            {
                return null;
            }
        }

        //获取参会人员的通过议题
        public string GetEmployeeTopic(string meetId)
        {
            return "";
        }
        // 修改会议状态
        public bool AlterStatus(string Id, string Status)
        {
            string sql = "Update Meeting Set Status = " + Status + " Where Id = " + Id;
            try
            {
                return DbHelperSQL.ExecuteSql(sql) > 0;
            }
            catch (Exception ex)
            {
                errorString = ex.Message;
                return false;
            }
        }

        //获取投票信息
        public string GetSelectVote(string meetingId)
        {
            string sql = " select *,(Select Theme From Vote Where Id = VoteId) As Theme from dbo.MeetingSurvey";
            if (meetingId != "")
            {
                sql += " Where MeetingId=" + meetingId;
            }
            DataSet ds = DbHelperSQL.Query(sql);
            if (ds == null) return "";
            Model.JsonGrid jr = new Model.JsonGrid();
            jr.total = ds.Tables[0].Rows.Count;
            jr.rows = ds.Tables[0];

            //Newtonsoft.Json.Converters.IsoDateTimeConverter timeConverter = new Newtonsoft.Json.Converters.IsoDateTimeConverter();
            //timeConverter.DateTimeFormat = "yyyy'-'MM'-'dd' 'HH':'mm':'ss";

            return JsonConvert.SerializeObject(jr);
        }
        //保存投票关联信息
        public bool SaveMeetingVote(string meetingId, string json)
        {
            List<Model.MeetingSurvey> temp = new List<Model.MeetingSurvey>();
            temp = JsonConvert.DeserializeObject<List<Model.MeetingSurvey>>(json);
            if (meetingId == "")
            {
                errorString = "没有找到会议，请先保存会议！";
                return false;
            }
            List<string> sqlList = new List<string>();
            string sql = "Delete From MeetingSurvey Where MeetingId ='" + meetingId + "' ";
            sqlList.Add(sql);
            foreach (Model.MeetingSurvey te in temp)
            {
                sql = " Insert MeetingSurvey(MeetingId,VoteId) ";
                sql += " Values('" + meetingId + "','" + te.VoteId + "')";
                sqlList.Add(sql);
            }

            //
            if (DbHelperSQL.ExecuteSqlTran(sqlList) > 0)
                return true;
            errorString = DbHelperSQL.errorString;
            return false;
        }

        //投票查询
        public string GetMeetingVote(int pageIndex, int pageSize, string filter)
        {
            try
            {
                string selectSql = @" Vote.*,MeetingId,(Select Name From Meeting Where Id = MeetingId) As MeetingName ";
                string whereCondition = filter;
                int nAllRecord = 0, nAllPages = 0;
                DataTable dt = DbHelperSQL.GetPage("  Vote.Id ", selectSql, "  MeetingSurvey Inner Join Vote On Vote.Id = MeetingSurvey.VoteId ", whereCondition, "",
                                pageIndex, pageSize, out nAllRecord, out nAllPages);
                if (dt == null) return "";
                Model.JsonGrid jr = new Model.JsonGrid();
                jr.total = nAllRecord;
                jr.rows = dt;

                Newtonsoft.Json.Converters.IsoDateTimeConverter timeConverter = new Newtonsoft.Json.Converters.IsoDateTimeConverter();
                timeConverter.DateTimeFormat = "yyyy'-'MM'-'dd' 'HH':'mm";

                return JsonConvert.SerializeObject(jr, timeConverter);
            }
            catch (Exception ex)
            {
                errorString = ex.Message;
                return "";
            }

        }


        public string DeleteMeeting(string Id)
        {
            List<string> sqlList = new List<string>();
            string sql = "Delete From dbo.MeetingEmployee Where MeetingId =" + Id;
            sqlList.Add(sql);
            sql = "Delete From  dbo.MeetingSurvey Where MeetingId =" + Id;
            sqlList.Add(sql);
            sql = "Delete From  dbo.MeetingTopic Where MeetingId =" + Id;
            sqlList.Add(sql);
            sql = "Delete From  dbo.MeetingTopicDocument Where MeetingId =" + Id;
            sqlList.Add(sql);
            sql = "Delete From  dbo.Meeting Where Id =" + Id;
            sqlList.Add(sql);
            Model.JsonResponse jr = new Model.JsonResponse();
            if (DbHelperSQL.ExecuteSqlTran(sqlList) > 0)
            {
                jr.success = true;
            }
            else
            {
                jr.success = false;
            }
            jr.Message = DbHelperSQL.errorString;
            return JsonConvert.SerializeObject(jr);
        }
    }

    public class TopicSave
    {
        public string TopicId = "";
        public string Title = "";
        public string EmployeeId = "";
        public string EmployeeName = "";
        public string EmployeeTitle = "";
        public string RoleId = "";
        public string Sort = "1000";
    }
}
